<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of record
 *
 * @author Juraj
 */
class record_obj {

    public function getTypeOfRecordById($id, $cols = array()) {
        $id = (int) $id;
        if (empty($id))
            return new error_obj('empty type of record id', get_class(), __FUNCTION__);

        $what = '';
        if (!empty($cols)) {
            foreach ($cols as $value) {
                $what .= $value . ',';
            }
            $what = substr($what, 0, strlen($what) - 1);
        } else {
            $what .= '*';
        }

        $db_obj = new db_obj();
        return $db_obj->getRow("SELECT $what FROM " . $GLOBALS['tb_type_of_records'] . " WHERE Id=$id");
    }

    public function getRecordTypes($order = '') {
        $db_obj = new db_obj();

        if (empty($order))
            $order = 'type_of_record ASC';

        $sql = "SELECT * FROM " . $GLOBALS['tb_type_of_records'] . " WHERE record=1 ORDER BY $order";
        base_obj::debug("getAllRecordTypes sql", $sql);

        return $db_obj->getAll($sql);
    }

    function getListOfRecords($filter = array(), $order = array(), $limit = '', $what = '*', $groupBy = '') {

        $db_obj = new db_obj();
        $other_table = '';
        $other_string = '';
        $connector = '';

        if (!empty($order)) {
            if (!empty($order['table'])) {
                $other_table = ', ' . $order['table'];
                $other_string = "ORDER BY " . $order['table'] . '.' . $order['orderBy'] . ' ' . $order['order'];
                $connector = 'AND ' . $GLOBALS['tb_records'] . '.' . $order['connector'];
            } else {
                $other_string = "ORDER BY " . $order['orderBy'] . ' ' . $order['order'];
            }
        }

        if ($limit != '')
            $limit = ' LIMIT ' . $limit;
        if ($groupBy != '')
            $groupBy = ' GROUP BY ' . $groupBy;
        if (strpos($what, 'COUNT') === false) {
            $what = $GLOBALS['tb_records'] . '.' . $what;
        }

        $where = 'WHERE 1=1';

        foreach ($filter as $key => $value) {
            if ($key == 'clients') {
                if (is_array($value)) {
                    $where_or .= " AND ( ";
                    foreach ($value as $k => $v) {
                        $where_or .= "client='" . $v . "' OR ";
                    }
                    $where_or = substr($where_or, 0, strlen($where_or) - 4);
                    $where_or .= " )";
                    $where .= $where_or;
                }
            } elseif ($key == 'ors' && !empty($value)) {
                $where_or = " AND ( ";
                foreach ($value as $k => $v) {
                    $where_or .= "(";
                    $temp = "";
                    foreach ($v as $subk => $subv) {
                        if ($subk == 'nottariff')
                            $temp .= " tariff!='" . $subv . "' AND ";
                        elseif ($subk == 'nottype')
                            $temp .= " type!='" . $subv . "' AND ";
                        else
                            $temp .= $subk . "='" . $subv . "' AND ";
                    }
                    $temp = substr($temp, 0, strlen($temp) - 5);
                    $where_or .= $temp;
                    $where_or .= ") OR ";
                }
                $where_or = substr($where_or, 0, strlen($where_or) - 4);
                $where_or .= " )";
                $where .= $where_or;
            } else {
                if ($key == 'month')
                    $where .= " AND DATE_FORMAT(date,'%c')='" . $value . "'";
                elseif ($key == 'year')
                    $where .= " AND DATE_FORMAT(date,'%Y')='" . $value . "'";
                elseif ($key == 'monthm')
                    $where .= " AND DATE_FORMAT(date,'%m')='" . $value . "'";
                elseif ($key == 'date-equal-higher')
                    $where .= " AND date>='" . $value . "'";
                elseif ($key == 'date-equal-lower')
                    $where .= " AND date<='" . $value . "'";
                elseif ($key == 'nottariff')
                    $where .= " AND tariff!='" . $value . "'";
                elseif ($key == 'nottype')
                    $where .= " AND type!='" . $value . "'";
                elseif ($key == 'client')
                    $where .= " AND " . $GLOBALS['tb_records'] . ".client='" . $value . "'";
                else
                    $where .= " AND " . $key . "='" . $value . "'";
            }
        }

        $sql = "SELECT $what FROM " . $GLOBALS['tb_records'] . " $other_table $where $connector $groupBy $other_string $limit";
        
        return $db_obj->getAll($sql);
    }

    public function checkAdd_record($check_data) {
        $result = array();
        foreach ($check_data as $key => $value) {
            switch ($key) {
                case 'client':
                    if ($value == -1)
                        $result[] = 'client';
                    break;
                case 'date':
                    if (empty($value))
                        $result[] = 'date';
                    break;
                case 'description':
                    if (empty($value))
                        $result[] = 'description';
                    break;
                case 'type':
                    if ($value == -1)
                        $result[] = 'type';
                    break;
                case 'amount':
                    if (!is_numeric($value) && !is_float($value))
                        $result[] = 'amount';
                    break;
                case 'price':
                    if (!is_numeric($value) && !is_float($value))
                        $result[] = 'price';
                    break;
                case 'amount_original':
                    if ($check_data['tariff'] == 6) {
                        $monthly = $this->get_monthly_unexpended_by_client($check_data['date'], $check_data['client']);
                        if (error_obj::isError($monthly))
                            $result[] = 'amount_count';
                        else {
                            $to_check = $check_data['amount'] - $value;
                            if ($to_check > $monthly)
                                $result[] = 'amount_count';
                        }
                    }
                    break;
            }
        }
        return $result;
    }

    public function addRecord($in_data) {
        if (empty($in_data)) {
            return new error_obj('empty $in_data', get_class(), __FUNCTION__);
        }
        $db_obj = new db_obj();
        $result = $db_obj->insert($GLOBALS['tb_records'], $in_data);
        if (error_obj::isError($result))
            return $result;
        else
            return $db_obj->lastInsertedId();
    }

    public function getRecordById($id, $cols = array()) {
        $id = (int) $id;
        if (empty($id))
            return new error_obj('empty record id', get_class(), __FUNCTION__);

        $what = '';
        if (!empty($cols)) {
            foreach ($cols as $value) {
                $what .= $value . ',';
            }
            $what = substr($what, 0, strlen($what) - 1);
        } else {
            $what .= '*';
        }

        $db_obj = new db_obj();
        return $db_obj->getRow("SELECT $what FROM " . $GLOBALS['tb_records'] . " WHERE recordId=$id");
    }

    public function deleteRecordById($id) {
        $id = (int) $id;
        if (empty($id)) {
            return new error_obj('deleteRecord -> empty $id', get_class(), __FUNCTION__);
        }
        $db_obj = new db_obj();

        $sql = "DELETE FROM " . $GLOBALS['tb_records'] . " WHERE recordId='" . $id . "'";

        $result = $db_obj->query($sql);
        return $result;
    }

    public function editRecord($in_data, $id) {
        $id = (int) $id;
        if (empty($id)) {
            return new error_obj('empty $id', get_class(), __FUNCTION__);
        }
        $db_obj = new db_obj();
        $keys = array('recordId' => $id);
        return $db_obj->update($GLOBALS['tb_records'], $in_data, $keys);
    }

    public function getListOfRecordsByFulltext($fulltext, $filter = array(), $order = array(), $limit = '', $what = '*') {
        $where = '';
        $db_obj = new db_obj();

        if (!empty($fulltext)) {
            $fulltext = strip_tags(trim($fulltext));

            $fulltext = explode(" ", $fulltext);
            $fulltextResults = array();
            foreach ($fulltext as $key => $word) {
                $sql = "SELECT " . $GLOBALS['tb_records'] . ".recordId 
                    FROM " . $GLOBALS['tb_records'] . ",
                         " . $GLOBALS['tb_clients'] . ",
                         " . $GLOBALS['tb_type_of_records'] . "   
                    WHERE 
                    " . $GLOBALS['tb_records'] . ".type = " . $GLOBALS['tb_type_of_records'] . ".Id AND 
                    " . $GLOBALS['tb_records'] . ".client = " . $GLOBALS['tb_clients'] . ".klient_id AND (  
                    " . $GLOBALS['tb_records'] . ".description LIKE '%" . $word . "%' OR 
                    " . $GLOBALS['tb_clients'] . ".name LIKE '%" . $word . "%' OR 
                    " . $GLOBALS['tb_type_of_records'] . ".type_of_record LIKE '%" . $word . "%'
                    )";
                $fulltextResults[] = array_unique($db_obj->getCol($sql));
            }
            if (isset($fulltextResults[0]) && !empty($fulltextResults[0])) {
                $temp = $fulltextResults[0];
                foreach ($fulltextResults as $key => $array) {
                    $temp = array_intersect($temp, $array);
                }
                if (!empty($temp)) {
                    $where .= 'WHERE (';
                    foreach ($temp as $id) {
                        $where .= "recordId='" . $id . "' OR ";
                    }
                    $where = substr($where, 0, $length - 4);
                    $where .= ')';
                } else {
                    $where .= 'WHERE 1=2';
                }
            } else {
                $where .= 'WHERE 1=2';
            }
        } else {
            $where .= 'WHERE 1=1';
        }

        $other_table = '';
        $other_string = '';
        $connector = '';

        if (!empty($order)) {
            if (!empty($order['table'])) {
                $other_table = ', ' . $order['table'];
                $other_string = "ORDER BY " . $order['table'] . '.' . $order['orderBy'] . ' ' . $order['order'];
                $connector = 'AND ' . $GLOBALS['tb_records'] . '.' . $order['connector'];
            } else {
                $other_string = "ORDER BY " . $order['orderBy'] . ' ' . $order['order'];
            }
        }
        if ($limit != '')
            $limit = ' LIMIT ' . $limit;
        if (strpos($what, 'COUNT') === false) {
            $what = $GLOBALS['tb_records'] . '.' . $what;
        }

        foreach ($filter as $key => $value) {
            if ($key == 'clients') {
                if (is_array($value)) {
                    $where_or .= " AND ( ";
                    foreach ($value as $k => $v) {
                        $where_or .= "client='" . $v . "' OR ";
                    }
                    $where_or = substr($where_or, 0, strlen($where_or) - 4);
                    $where_or .= " )";
                    $where .= $where_or;
                }
            } else {
                if ($key == 'month')
                    $where .= " AND DATE_FORMAT(date,'%c')='" . $value . "'";
                elseif ($key == 'year')
                    $where .= " AND DATE_FORMAT(date,'%Y')='" . $value . "'";
                elseif ($key == 'monthm')
                    $where .= " AND DATE_FORMAT(date,'%m')='" . $value . "'";
                elseif ($key == 'date-equal-higher')
                    $where .= " AND date>='" . $value . "'";
                elseif ($key == 'date-equal-lower')
                    $where .= " AND date<='" . $value . "'";
                elseif ($key == 'nottariff')
                    $where .= " AND tariff!='" . $value . "'";
                elseif ($key == 'nottype')
                    $where .= " AND type!='" . $value . "'";
                elseif ($key == 'client')
                    $where .= " AND " . $GLOBALS['tb_records'] . ".client='" . $value . "'";
                else
                    $where .= " AND " . $key . "='" . $value . "'";
            }
        }

        $sql = "SELECT $what FROM " . $GLOBALS['tb_records'] . " $other_table $where $connector $other_string $limit";
        
        return $db_obj->getAll($sql);
    }

    public function get_monthly_unexpended_by_client($date, $client) {
        $client = (int) $client;
        if ($client < 1)
            return new error_obj('empty client', __CLASS__, __FUNCTION__);

        $date = strip_tags($date);
        if (empty($date))
            return new error_obj('empty date', __CLASS__, __FUNCTION__);

        $date = explode('/', $date);
        if (count($date) != 3)
            return new error_obj('bad date format', __CLASS__, __FUNCTION__);

        if (!($date[1] > 0 && $date[1] <= 12))
            return new error_obj('bad date month', __CLASS__, __FUNCTION__);

        if (!($date[2] > 2010 && $date[1] <= 2030))
            return new error_obj('bad date year', __CLASS__, __FUNCTION__);

        $month = (int) $date[1];
        $year = (int) $date[2];

        $client_obj = new client_obj();

        $client_data = $client_obj->getClient($client);

        if (error_obj::isError($client_data))
            return $client_data;

        if (empty($client_data))
            return new error_obj('empty client data', __CLASS__, __FUNCTION__);

        $filter = array(
            'tariff' => 6,
            'client' => $client,
            'month' => $month,
            'year' => $year
        );
        $records = $this->getListOfRecords($filter, array(), '', 'amount');

        if (error_obj::isError($record))
            return $records;

        $sum = 0;

        if (!empty($records)) {
            foreach ($records as $key => $record) {
                $sum += $record['amount'];
            }
        }

        return ($client_data['monthlyPrepaid'] - $sum);
    }
    
    public function visibleRecord($id) {
        if ($_SESSION['user']['profile']['userlevel'] != USER_TYPE_CLIENT)
            return true;

        $id = (int) $id;
        if (empty($id))
            return false;

        $record = $this->getRecordById($id);

        if (isset($_SESSION['user']['clients'][$record['client']]) && !empty($_SESSION['user']['clients'][$record['client']]))
            return true;

        return false;
    }
    
    public function getActiveMonths($task) {
        
        $task = (int) $task;
        if (empty($task))
            return array();
        
        $db_obj = new db_obj();
        
        $sql = "SELECT MIN( date ) AS date_min, MAX( date ) AS date_max FROM `records` WHERE taskId ='$task'";
        
        $dates = $db_obj->getRow($sql);
        
        if (error_obj::isError($dates))
            return array();
        elseif (empty($dates[0]) || empty($dates[1]))
            return array();
        
        $end = base_obj::convertDateTo($dates[1], 'U', 'date');
        $start = base_obj::convertDateTo($dates[0], 'U', 'date');
        
        $startM = date("m", $start);
        $startY = date("Y", $start);
        $endM = date("m", $end);
        $endY = date("Y", $end);
        
        $poc = 0;
        
        if ($startY == $endY) {
            if ($startM > $endM)
                $poc = 0;
            else 
                $poc = (($endM - $startM) + 1);
        } elseif ($startY < $endY) {
            $poc = (($endM - $startM) + 12 + 1);
        } else {
            $poc = 0;
        }
        
        $months = array ();
        
        for ($i = 0; $i < $poc; $i ++) {
            $temp = mktime(12, 0, 0, $startM + $i, 1, $startY);
            $months[date("m", $temp).'_'.date("Y", $temp)] = array(date("m", $temp), date("Y", $temp));
        }
        
        return $months;
    }
    
    public function compareRecords($originalRecord, $updatedRecord) {
        $out_data = array();
        $user_obj = new user_obj();
        $client_obj = new client_obj();
        $task_obj = new task_obj();
        $tariff_obj = new tariff_obj();
        foreach ($updatedRecord as $key => $value) {
            $not_compare = array('after_hours', 'last_editor', 'last_update', 'recordId');
            if (!is_numeric($key) && !in_array($key, $not_compare)) {
                if (trim($value) != trim($originalRecord[$key])) {
                    $can_compare = true;
                    switch ($key) {
                        case 'date':
                            $originalValue = base_obj::convertDateTo($originalRecord[$key], 'dd/mm/yyyy', 'date');
                            $newValue = base_obj::convertDateTo($value, 'dd/mm/yyyy', 'date');
                            break;
                        case 'client':
                            $originalValue = $client_obj->getClient($originalRecord[$key], array("name"));
                            $originalValue = $originalValue['name'];
                            $newValue = $client_obj->getClient($value, array("name"));
                            $newValue = $newValue['name'];
                            break;
                        case 'type':
                            $originalValue = $this->getTypeOfRecordById($originalRecord[$key]);
                            $originalValue = $originalValue['type_of_record'];
                            $newValue = $this->getTypeOfRecordById($value);
                            $newValue = $newValue['type_of_record'];
                            break;
                        case 'tariff':
                            if ($originalRecord[$key] != -1){
                                $originalValue = $tariff_obj->getTariffById($originalRecord[$key]);
                                $originalValue = $originalValue['title'];
                            } else {
                                $originalValue = '-';
                            }
                            
                            if ($value != -1){
                                $newValue = $tariff_obj->getTariffById($value);
                                $newValue = $newValue['title'];
                            } else {
                                $newValue = '-';
                            }
                            break;
                        case 'user_id':
                            $originalValue = $user_obj->getUserById($originalRecord[$key], array("name", "surname"));
                            $originalValue = $originalValue['name'] . ' ' . $originalValue['surname'];
                            $newValue = $user_obj->getUserById($value, array("name", "surname"));
                            $newValue = $newValue['name'] . ' ' . $newValue['surname'];
                            break;
                        case 'taskId':
                            $originalValue = $task_obj->getTaskById($originalRecord[$key]);
                            if (error_obj::isError($originalValue)){
                                $can_compare = false;
                                break;
                            }
                            $originalValue = $originalValue['title'];
                            $newValue = $task_obj->getTaskById($value);
                            if (error_obj::isError($newValue)){
                                $can_compare = false;
                                break;
                            }
                            $newValue = $newValue['title'];
                            break;
                        case 'attention':
                            $originalValue = !empty($originalRecord[$key])? 'Yes':'No';
                            $newValue = !empty($value)? 'Yes':'No';
                            break;
                        default:
                            $originalValue = $originalRecord[$key];
                            $newValue = $value;
                            break;
                    }
                    if ($can_compare) {
                        $out_data[$key] = array(
                            'original' => $originalValue,
                            'new' => $newValue
                        );
                    }
                }
            }
        }
        return $out_data;
    }

}

?>
